| 实战案例 | 您所在的位置：网站首页 › sqlserver 集群切换数据库里的数据不完整 › 实战案例 |
实战案例
|
目录
一、认识读写分离
01、读写分离概念
读写分离：基本的原理是让主数据库处理事务性增（insert）、改（update）、删（delete）操作而从数据库处理查询（select）操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。 因为数据库的“写”（写100,00条数据到MySQL可能要3分钟）操作是比较 耗时的，但是数据库的“读”（从MySQL读100,00条数据可能只要5秒钟）。所以读写分离可以解决数据库写入时影响查询效率的问题。 03、读写分离应用场景数据库不一定总要读写分离，如果程序使用数据库较多、更新较少、查询较 多的情况下会考虑使用。利用数据库主从同步，可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。例如，使用memcache、分表、搜索引擎等方法。 04、主从复制、读写分离的基本设计在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过一台主、多台从节点，主节点提供写操作，从节点提供读操作来实现读写分离，从而提升数据库的并发负载能力。 二、 案例分析 01、节点规划 IP主机名节点192.168.62.10MycatMycat中间件服务节点192.168.62.20db1MariaDB数据库集群主节点192.168.62.30db2MariaDB数据库集群从节点 02、基础准备使用CentOS7.2系统，flavor使用2vCPU/4G内存/50G硬盘，创建3台虚拟机进行实验。 其中2台虚拟机db1和db2部署MariaDB数据库服务，搭建主从数据库集群；一台作为主节点，负责写入数据库信息；另一台作为从节点，负责读取数据库信息。 使用一台虚拟机部署Mycat数据库中间件服务，将用户提交的读写操作识别分发给相应的数据库节点。这样将用户的访问操作、数据库的读与写操作分给3台主机，只有数据库集群的主节点接收增、删、改SQL语句，从节点接收查询语句，分担了主节点的查询压力。 Yum源使用提供的gpmall-repo文件夹作为本地源，Mycat组件使用提供的Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz压缩包安装。 三、案例实施 1、基础环境配置（1）修改主机名 使用 hostnamectl 命令修改3台主机的主机名： [root@localhost~]hostnamectl set-hostname mycat [root@localhost~]bash [root@mycat~]# [root@localhost~]hostnamectl set-hostname db1 [root@localhost~]bash [root@db1~]# [root@localhost~]hostnamectl set-hostname db2 [root@localhost~]bash [root@db2~]#（2）编辑 hosts 文件 3台虚拟机的 /etc/hosts 文件配置部分： [root@mycat ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.62.10 mycat 192.168.62.20 db1 192.168.62.30 db2（3）配置Yum安装源 数据库集群需要安装 MariaDB 数据库服务，需要给集群虚拟机配置Yum安装源文件，使用提供的 gpmall-repo 文件上传至3个虚拟机的/opt目录下，设置本地Yum源。 首先将3个节点/etc/yum.repo.d目录下的文件移动到/media下，命令如下： mv/etc/yum.repos.d/*/media/数据库集群虚拟机的Yum安装源文件配置部分： # http://downloads.mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.2/centos7-ppc64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1（4）安装JDK环境 Mycate 节点安装Java环境: yum install -y jdk-8u321-linux-x64.rpm 2、部署MariaDB主从数据库集群服务（1）安装MariaDB服务 通过YUM命令在db1和db2虚拟机节点上安装MariaDB服务，命令如下： yum install mariadb mariadb-server -y2个节点启动MariaDB服务，并设置MariaDB服务为开机自启。 systemctl start mariadb systemctl enable mariadb（2）初始化MariaDB数据库 在db1和db2虚拟机节点上初始化MariaDB数据库，并设置MariaDB数据库root访问用户的密码为kuaicdn。 [root@db1~]mysql_secure_installation … Entercurrentpasswordforroot(enterfornone): ##默认按回车 Setrootpassword?[Y/n]y Newpassword: ##输入数据库root密码123456 Re-enternewpassword: ##重复输入密码123456 Removeanonymoususers?[Y/n]y Disallowrootloginremotely?[Y/n]n Removetestdatabaseandaccesstoit?[Y/n]y Reloadprivilegetablesnow?[Y/n]y（3）配置数据库集群主节点 编辑主节点db1虚拟机的数据库配置文件my.cnf，在配置文件my.cnf中增添下面的内容： [root@db1~]cat/etc/my.cnf [mysqld] log_bin=mysql-bin #记录操作日志 binlog_ignore_db=mysql #不同步MySQL系统数据库 server_id=18 #数据库集群中的每个节点id都要不同 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid编辑完成配置文件my.cnf后，重启MariaDB服务。 [root@db1~]systemctl restart mariadb（4）开放主节点的数据库权限 在主节点db1虚拟机上使用mysql命令登录MariaDB数据库，授权在任何客户端机器上可以以root用户登录到数据库。 [root@db1~]#mysql-uroot-p123456 MariaDB[(none)]>grant all privileges on *.* to root@'%'identified by "kuaicdn"; Query OK, 0 rows affected (0.00 sec)在主节点db1数据库上创建一个user用户让从节点db2连接，并赋予从节点同步主节点数据库的权限，命令如下： MariaDB [(none)]> grant replication slave on *.* to 'user'@'db2'identified by 'kuaicdn'; Query OK, 0 rows affected (0.00 sec)（5）配置从节点db2同步主节点db1 在从节点db2虚拟机上使用mysql命令登录MariaDB数据库，配置从节点连接主节点的连接信息。master_host为主节点主机名db1，master_user为在步骤（4）中创建的用户user，命令如下： [root@db1 ~] mysql -uroot -pkuaicdn MariaDB [(none)]> change master to master_host='db1',master_user='user',master_password='kuaicdn'; Query OK, 0 rows affected (0.08 sec)配置完毕主从数据库之间的连接信息之后，开启从节点服务。使用showslavestatus\G;命令并查看从节点服务状态，如果Slave_IO_Running和Slave_SQL_Running的状态都为YES，则从节点服务开启成功。 MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) # 查看启动状态 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: db1 Master_User: user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-relay-bin.000001 Read_Master_Log_Pos: 6274 Relay_Log_File: mysql-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 6274 Relay_Log_Space: 534 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 104 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)（6）验证主从数据库的同步功能 先在主节点db1的数据库中创建库test，并在库test中创建表company，插入表数据。创建完成后，查看表company数据，如下所示： MariaDB[(none)]>create databasetest; Query OK,1 row affected (0.001sec) MariaDB[(none)]>use test Database changed MariaDB[test]>create table company(id int not null primary key,name varchar(50),addr varchar(255)); Query OK,0 rows affected (0.165sec) MariaDB[test]>insert into company values(1,"facebook","usa"); Query OK,1 row affected (0.062sec) MariaDB [test]>select * from company; +----+----------+------+ |id|name|addr| +----+----------+------+ |1|facebook|usa| +----+----------+------+ 1 row inset (0.000sec)这时从节点db2的数据库就会同步主节点数据库创建的 test 库，可以在从节点查询 test 数据库与表company，如果可以查询到信息，就能验证主从数据库集群功能在正常运行。 查询结果如下所示： MariaDB[(none)]>show databases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |test| +--------------------+ 4 row sinset (0.001sec) MariaDB[(none)]>select * from test.company; +----+----------+------+ |id|name|addr| +----+----------+------+ |1|facebook|usa| +----+----------+------+ 1 row inset (0.001sec) 3.部署Mycat读写分离中间件服务（1）安装Mycat服务 将Mycat服务的二进制软件包Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz上传 到Mycat虚拟机的/root目录下，并将软件包解压到/use/local目录中。赋予解压后的Mycat目录权限。 [root@mycat~]tar-zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/ [root@mycat~]chown -R 777 /usr/local/mycat/在 /etc/profile 系统变量文件中添加Mycat服务的系统变量，并生效变量。 [root@mycat~]echo export MYCAT_HOME=/usr/local/mycat/>>/etc/profile [root@mycat~]source /etc/profile（2）编辑Mycat的逻辑库配置文件 [root@mycat~]cat /usr/local/mycat/conf/schema.xml selectuser() ##IP需要修改成实际的IP地址（3）修改配置文件权限 修改schema.xml的用户权限，命令如下： [root@mycat~]chown root:root /usr/local/mycat/conf/schema.xml（4）编辑mycat的访问用户 修改 /usr/local/mycat/conf/ 目录下的server.xml文件，修改 root 用户的访问密码与数据库，密码 设置为123456，访问Mycat的逻辑库为 USERDB，命令如下： [root@mycat~]#cat/usr/local/mycat/conf/server.xml 123456 USERDB保存并退出server.xml配置文件。 （5）启动Mycat服务 通过命令启动Mycat数据库中间件服务，启动后使用 netstat-ntpl 命令查看虚拟机端口开放情 况，如果有开放 8066 和 9066 端口，则表示Mycat服务开启成功。 [root@mycat~] /bin/bash/usr/local/mycat/bin/mycatstart
（1）用Mycat服务查询数据库信息 先在Mycat虚拟机上使用Yum安装mariadb-client服务。 [root@mycat~]yum install -y MariaDB-client在 Mycat 虚拟机上使用mysql命令查看Mycat服务的逻辑库USERDB，因为Mycat的逻辑库USERDB对应数据库test。所以可以查看库中已经创建的表company。 [root@mycat~]mysql -h 127.0.0.1 -P8066 -uroot -p123456 MySQL[(none)]>show databases; +----------+ |DATABASE| +----------+ |USERDB| +----------+ 1 row inset (0.001sec) MySQL[(none)]>use USERDB Database changed MySQL[USERDB]>show tables; +----------------+ |Tables_in_test| +----------------+ |company| +----------------+ 1 row inset (0.003sec) MySQL[USERDB]>select * from company; +----+----------+------+ |id|name|addr| +----+----------+------+ |1|facebook|usa| +----+----------+------+ 1rowinset(0.005sec)（2）用Mycat服务添加表数据 在Mycat虚拟机上使用mysql命令对表company添加一条数据(2,“basketball”,“usa”)，添加完毕后查看表信息。 MySQL[USERDB]>insert into company values(2,"bastetball","usa"); Query OK,1 row affected (0.050sec) MySQL[USERDB]>select * from company; +----+------------+------+ |id|name|addr| +----+------------+------+ |1|facebook|usa| |2|bastetball|usa| +----+------------+------+ 2 rows in set(0.002sec)（2）用Mycat服务添加表数据 在Mycat虚拟机上使用mysql命令对表company添加一条数据(2,“basketball”,“usa”)，添加完毕后查看表信息。 MySQL[USERDB]>insert into company values(2,"bastetball","usa"); Query OK,1 row affected (0.050sec) MySQL[USERDB]> select * from company; +----+------------+------+ |id|name|addr| +----+------------+------+ |1|facebook|usa| |2|bastetball|usa| +----+------------+------+ 2rowsinset(0.002sec)（3）验证Mycat服务对数据库读写操作分离 在Mycat虚拟机节点使用mysql命令，通过9066端口查询对数据库读写操作的分离信息。可以看到所有的写入操作WRITE_LOAD数都在db1主数据库节点上，所有的读取操作 READ_LOAD 数都在db2 主数据库节点上。由此可见，数据库读写操作已经分离到db1和db2节点上了。 [root@mycat~]mysql -h127.0.0.1 -P9066 -uroot -p123456 -e'show @@datasource;'查询结果如图 |
至此，Mycat读写分离数据库案例完成。【本文地址】